package com.softserveinc.data.dal;

import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.List;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.util.Log;

import com.softserveinc.data.help.DBDataProcessor;
import com.softserveinc.data.help.MovieDBOpenHelper;
import com.softserveinc.data.help.MovieDBOpenHelper.DataListsTableMeta;
import com.softserveinc.data.help.MovieDBOpenHelper.MoviesDataListsTableMeta;
import com.softserveinc.data.help.MovieDBOpenHelper.MoviesSearchTableMeta;
import com.softserveinc.data.help.MovieDBOpenHelper.MoviesTableMeta;
import com.softserveinc.data.help.MovieListType;
import com.softserveinc.model.DataListMeta;
import com.softserveinc.model.Movie;

//TODO delete unused movies.

public class MovieDao implements IMovieDao {

	private static final String TAG = MovieDao.class.getSimpleName();

	private static final String DATE_FORMAT = "yyyy-MM-dd";

	private static final long VALID_PERIOD = 600;

	private static final long MILLISECONDS_IN_MINUTE = 1000 * 60;

	private final MovieDBOpenHelper helper;

	private SQLiteDatabase database;

	private final DBDataProcessor dataProcessor;

	public MovieDao(final Context context) {
		helper = new MovieDBOpenHelper(context);
		database = helper.getWritableDatabase();

		dataProcessor = new DBDataProcessor();
	}

	@Override
	public List<Movie> getListByOrder(final MovieListType listOrder) {
		DataListMeta dataListMeta = getDataListMeta(listOrder);

		if (dataListMeta == null || !isDataValid(dataListMeta)) {
			return null;
		}

		final String moviesTable = MoviesTableMeta.TABLE_NAME;
		final String moviesIDColumn = MoviesTableMeta.COLUMN_NAME_TMDB_ID;

		final String moviesDataListTable = MoviesDataListsTableMeta.TABLE_NAME;

		final String moviesIDRefColumn = MoviesDataListsTableMeta.COLUMN_NAME_MOVIE_TMDB_ID_REF;
		final String dataListsIDRefColumn = MoviesDataListsTableMeta.COLUMN_NAME_LIST_ID_REF;

		String sql = "SELECT " + moviesTable + ".* FROM " + moviesTable
				+ " INNER JOIN " + moviesDataListTable + " ON " + moviesTable
				+ "." + moviesIDColumn + "=" + moviesDataListTable + "."
				+ moviesIDRefColumn + " WHERE " + moviesDataListTable + "."
				+ dataListsIDRefColumn + "=?";

		Log.d(TAG, sql);

		Cursor cursor = database.rawQuery(sql,
				new String[] { "" + dataListMeta.getID() });

		return dataProcessor.parseMovieListCursor(cursor);
	}

	private DataListMeta getDataListMeta(final MovieListType listOrder) {
		String tableName = DataListsTableMeta.TABLE_NAME;

		String typeColumn = DataListsTableMeta.COLUMN_NAME_LIST_TYPE;
		String idColumn = DataListsTableMeta.COLUMN_NAME_LIST_ID;
		String lastModifiedColumn = DataListsTableMeta.COLUMN_NAME_LAST_MODIFIED;

		String sql = "SELECT * FROM " + tableName + " WHERE " + typeColumn
				+ "=?";

		Cursor cursor = database.rawQuery(sql,
				new String[] { listOrder.name() });

		if (cursor.moveToFirst()) {
			long id = cursor.getLong(cursor.getColumnIndex(idColumn));

			long lastModified = cursor.getInt(cursor
					.getColumnIndex(lastModifiedColumn));

			return new DataListMeta(id, listOrder.name(), lastModified);
		}

		return null;
	}

	private boolean isDataValid(final DataListMeta dataListMeta) {
		long currentTime = new Date().getTime() / MILLISECONDS_IN_MINUTE;

		Log.d(TAG, "current " + currentTime);
		Log.d(TAG, "stored " + dataListMeta.getLastModified());

		return (currentTime - dataListMeta.getLastModified()) < VALID_PERIOD;
	}

	@Override
	public List<Movie> getListBySearchQuery(final String requestedQuery) {
		String availableQuery = getSearchValueFormDB();

		if (availableQuery != null
				&& availableQuery.equalsIgnoreCase(requestedQuery)) {

			return getListByOrder(MovieListType.SEARCH);
		}

		return null;
	}

	private String getSearchValueFormDB() {
		String tableName = MoviesSearchTableMeta.TABLE_NAME;

		String sql = "SELECT * FROM " + tableName;

		Cursor cursor = database.rawQuery(sql, null);

		if (cursor.moveToFirst()) {
			String searchValueColumn = MoviesSearchTableMeta.COLUMN_NAME_SEARCH_KEY;
			return cursor.getString(cursor.getColumnIndex(searchValueColumn));
		}

		return null;
	}

	@Override
	public void insertMovieList(final List<Movie> movies,
			final MovieListType listOrder) {
		DataListMeta dataListMeta = getDataListMeta(listOrder);

		if (dataListMeta == null) {
			dataListMeta = insertDataList(listOrder);
		} else {
			updateDataList(dataListMeta);
		}

		deleteRefTableConnection(dataListMeta);

		for (Movie movie : movies) {
			insertOrUpdate(movie);

			updateRefTableStatement(movie.getId(), dataListMeta.getID());
		}
	}

	@Override
	public void addMovieList(final List<Movie> movies,
			final MovieListType listOrder) {
		DataListMeta dataListMeta = getDataListMeta(listOrder);

		if (dataListMeta == null) {
			dataListMeta = insertDataList(listOrder);
		} else {
			updateDataList(dataListMeta);
		}

		for (Movie movie : movies) {
			Log.d(TAG, movie.getTitle());
			insertOrUpdate(movie);

			updateRefTableStatement(movie.getId(), dataListMeta.getID());
		}
	}

	@Override
	public void insertSearchedMovieList(final String searchQuery,
			final List<Movie> movies) {
		ContentValues values = new ContentValues();

		values.put(MoviesSearchTableMeta.COLUMN_NAME_SEARCH_KEY, searchQuery);

		database.delete(MoviesSearchTableMeta.TABLE_NAME, null, null);
		database.insert(MoviesSearchTableMeta.TABLE_NAME, null, values);

		insertMovieList(movies, MovieListType.SEARCH);
	}

	private void updateRefTableStatement(final int movieID, final long listID) {
		if (!isMovieListRefInDB(movieID, listID)) {
			ContentValues values = new ContentValues();

			values.put(MoviesDataListsTableMeta.COLUMN_NAME_LIST_ID_REF, listID);
			values.put(MoviesDataListsTableMeta.COLUMN_NAME_MOVIE_TMDB_ID_REF,
					movieID);

			database.insert(MoviesDataListsTableMeta.TABLE_NAME, null, values);
		}
	}

	private void updateDataList(final DataListMeta dataListMeta) {
		ContentValues values = new ContentValues();

		long currentTime = new Date().getTime() / MILLISECONDS_IN_MINUTE;
		values.put(DataListsTableMeta.COLUMN_NAME_LAST_MODIFIED, currentTime);

		values.put(DataListsTableMeta.COLUMN_NAME_LIST_TYPE,
				dataListMeta.getListType());

		String tableName = DataListsTableMeta.TABLE_NAME;
		String listIDColumn = DataListsTableMeta.COLUMN_NAME_LIST_ID;

		database.update(tableName, values, listIDColumn + "=?",
				new String[] { "" + dataListMeta.getID() });
	}

	private DataListMeta insertDataList(final MovieListType listOrder) {
		ContentValues values = new ContentValues();

		long currentTime = new Date().getTime() / MILLISECONDS_IN_MINUTE;
		values.put(DataListsTableMeta.COLUMN_NAME_LAST_MODIFIED, currentTime);

		String order = listOrder.name();
		values.put(DataListsTableMeta.COLUMN_NAME_LIST_TYPE, order);

		long id = database.insert(DataListsTableMeta.TABLE_NAME, null, values);

		return new DataListMeta(id, order, currentTime);
	}

	@Override
	public void insertOrUpdate(final Movie movie) {
		int movieID = movie.getId();

		ContentValues values = createMovieValues(movie);

		if (isMovieAvailableInDB(movieID)) {
			updateMovie(movieID, values);
		} else {
			insertMovie(movieID, values);
		}
	}

	private void updateMovie(final int movieID, final ContentValues values) {
		database = helper.getWritableDatabase();

		String tableName = MoviesTableMeta.TABLE_NAME;
		String movieIDColumn = MoviesTableMeta.COLUMN_NAME_TMDB_ID;

		database.update(tableName, values, movieIDColumn + "=?",
				new String[] { "" + movieID });
	}

	private void insertMovie(final int movieID, final ContentValues values) {
		database = helper.getWritableDatabase();

		database.insert(MoviesTableMeta.TABLE_NAME, null, values);
	}

	@Override
	public void deleteMovie(final Movie movie) {

	}

	private void deleteRefTableConnection(final DataListMeta dataListMeta) {
		String tableName = MoviesDataListsTableMeta.TABLE_NAME;
		String listIDRef = MoviesDataListsTableMeta.COLUMN_NAME_LIST_ID_REF;

		database.delete(tableName, listIDRef + "=?", new String[] { ""
				+ dataListMeta.getID() });
	}

	private ContentValues createMovieValues(final Movie movie) {
		ContentValues values = new ContentValues();

		values.put(MoviesTableMeta.COLUMN_NAME_TITLE, movie.getTitle());
		values.put(MoviesTableMeta.COLUMN_NAME_POPULARITY,
				movie.getPopularity());
		values.put(MoviesTableMeta.COLUMN_NAME_POSTER_URL,
				movie.getPosterHttpUrl());
		values.put(MoviesTableMeta.COLUMN_NAME_POSTER_LOCAL_URL,
				movie.getPosterLocalUrl());
		values.put(MoviesTableMeta.COLUMN_NAME_CERTIFICATION_NAME, movie
				.getCertification().toString());
		values.put(MoviesTableMeta.COLUMN_NAME_TMDB_ID, movie.getId());

		SimpleDateFormat format = new SimpleDateFormat(DATE_FORMAT);

		try {
			values.put(MoviesTableMeta.COLUMN_NAME_RELEASE_DATE,
					format.format(movie.getReleaseDate()));
		} catch (Exception e) {
			Log.e(TAG, e.getMessage());
		}

		return values;
	}

	private boolean isMovieAvailableInDB(final Integer movieID) {
		String tableName = MoviesTableMeta.TABLE_NAME;

		String movieIDColumn = MoviesTableMeta.COLUMN_NAME_TMDB_ID;

		String sql = "SELECT * FROM " + tableName + " WHERE " + movieIDColumn
				+ "=?";

		database = helper.getWritableDatabase();

		Cursor cursor = database.rawQuery(sql, new String[] { "" + movieID });

		boolean condition = cursor.moveToFirst();

		cursor.close();

		return condition;
	}

	private boolean isMovieListRefInDB(final int movieID, final long listID) {
		String tableName = MoviesDataListsTableMeta.TABLE_NAME;
		String movieIDColumn = MoviesDataListsTableMeta.COLUMN_NAME_MOVIE_TMDB_ID_REF;
		String listIDColumn = MoviesDataListsTableMeta.COLUMN_NAME_LIST_ID_REF;

		String sql = "SELECT * FROM " + tableName + " WHERE " + movieIDColumn
				+ "=? AND " + listIDColumn + "=?";

		Cursor cursor = database.rawQuery(sql, new String[] { "" + movieID,
				"" + listID });

		boolean condition = cursor.moveToFirst();

		cursor.close();

		return condition;
	}
}
